﻿'
' DotNetNuke® - http://www.dotnetnuke.com
' Copyright (c) 2002-2008
' by DotNetNuke Corporation
'
' Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated 
' documentation files (the "Software"), to deal in the Software without restriction, including without limitation 
' the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and 
' to permit persons to whom the Software is furnished to do so, subject to the following conditions:
'
' The above copyright notice and this permission notice shall be included in all copies or substantial portions 
' of the Software.
'
' THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED 
' TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL 
' THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF 
' CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER 
' DEALINGS IN THE SOFTWARE.
'

Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Text

Namespace DotNetNuke.Modules.IWebCF
    Public Class DAL

        Public Shared Function CheckIWebSettings(ByVal PortalID As Integer, ByVal SettingID As String) As Integer
            Dim i As Integer = 0
            Dim IWebInfo As IWebInfo = New IWebInfo()
            Dim mySqlString As New StringBuilder()

            mySqlString.Append("SELECT Setting FROM {objectQualifier}IWebCFSettings ")
            mySqlString.Append("WHERE ")
            mySqlString.Append("(PortalID = @PortalID) ")
            mySqlString.Append("AND ")
            mySqlString.Append("(SettingID = @SettingID) ")

            Dim parmPortalID As SqlParameter = New SqlParameter("@PortalID", SqlDbType.Int, 4)
            Dim parmSettingID As SqlParameter = New SqlParameter("@SettingID", SqlDbType.NVarChar, 100)

            parmPortalID.Value = PortalID
            parmSettingID.Value = SettingID

            Using dr As IDataReader = CType(DotNetNuke.Data.DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), parmPortalID, parmSettingID), IDataReader)
                While dr.Read
                    i = i + 1
                End While
            End Using
            Return i
        End Function

        Public Shared Function GetIWebSettings(ByVal PortalID As Integer, ByVal SettingID As String) As String
            Dim IWebInfo As IWebInfo = New IWebInfo()
            Dim mySqlString As New StringBuilder()

            mySqlString.Append("SELECT Setting FROM {objectQualifier}IWebCFSettings ")
            mySqlString.Append("WHERE ")
            mySqlString.Append("(PortalID = @PortalID) ")
            mySqlString.Append("AND ")
            mySqlString.Append("(SettingID = @SettingID) ")

            Dim parmPortalID As SqlParameter = New SqlParameter("@PortalID", SqlDbType.Int, 4)
            Dim parmSettingID As SqlParameter = New SqlParameter("@SettingID", SqlDbType.NVarChar, 100)

            parmPortalID.Value = PortalID
            parmSettingID.Value = SettingID

            Using dr As IDataReader = CType(DotNetNuke.Data.DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), parmPortalID, parmSettingID), IDataReader)

                While dr.Read
                    IWebInfo.Setting = Convert.ToString(dr("Setting"))
                End While
            End Using
            Return IWebInfo.Setting
        End Function

        Public Shared Sub DeleteIWebSettings(ByVal colIWebInfo As IWebInfo)
            Dim mySqlString As New StringBuilder()

            mySqlString.Append("DELETE FROM {objectQualifier}IWebCFSettings ")
            mySqlString.Append("WHERE ")
            mySqlString.Append("(PortalID = @PortalID) ")
            mySqlString.Append("AND ")
            mySqlString.Append("(SettingID = @SettingID) ")
            mySqlString.Append("AND ")
            mySqlString.Append("(Setting = @Setting) ")

            Dim parmPortalID As SqlParameter = New SqlParameter("@PortalID", SqlDbType.Int, 4)
            Dim parmSettingID As SqlParameter = New SqlParameter("@SettingID", SqlDbType.NVarChar, 100)
            Dim parmSetting As SqlParameter = New SqlParameter("@Setting", SqlDbType.NVarChar, 100)

            parmPortalID.Value = colIWebInfo.PortalID
            parmSettingID.Value = colIWebInfo.SettingID
            parmSetting.Value = colIWebInfo.Setting

            DotNetNuke.Data.DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), parmPortalID, parmSettingID, parmSetting)
        End Sub

        Public Shared Sub InsertIWebSettings(ByVal colIWebInfo As IWebInfo)
            Dim mySqlString As New StringBuilder()

            mySqlString.Append("INSERT INTO {objectQualifier}IWebCFSettings ")
            mySqlString.Append("(PortalID, SettingID, Setting) ")
            mySqlString.Append("VALUES ")
            mySqlString.Append("(@PortalID,@SettingID,@Setting) ")

            Dim parmPortalID As SqlParameter = New SqlParameter("@PortalID", SqlDbType.Int, 4)
            Dim parmSettingID As SqlParameter = New SqlParameter("@SettingID", SqlDbType.NVarChar, 100)
            Dim parmSetting As SqlParameter = New SqlParameter("@Setting", SqlDbType.NVarChar, 100)

            parmPortalID.Value = colIWebInfo.PortalID
            parmSettingID.Value = colIWebInfo.SettingID
            parmSetting.Value = colIWebInfo.Setting

            DotNetNuke.Data.DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), parmPortalID, parmSettingID, parmSetting)
        End Sub

        Public Shared Sub UpdateIWebSettings(ByVal colIWebInfo As IWebInfo)
            Dim mySqlString As New StringBuilder()

            mySqlString.Append("UPDATE {objectQualifier}IWebCFSettings ")
            mySqlString.Append("SET Setting = @Setting ")
            mySqlString.Append("WHERE ")
            mySqlString.Append("(PortalID = @PortalID) AND (SettingID = @SettingID) ")

            Dim parmPortalID As SqlParameter = New SqlParameter("@PortalID", SqlDbType.Int, 4)
            Dim parmSettingID As SqlParameter = New SqlParameter("@SettingID", SqlDbType.NVarChar, 100)
            Dim parmSetting As SqlParameter = New SqlParameter("@Setting", SqlDbType.NVarChar, 100)

            parmPortalID.Value = colIWebInfo.PortalID
            parmSettingID.Value = colIWebInfo.SettingID
            parmSetting.Value = colIWebInfo.Setting

            DotNetNuke.Data.DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), parmPortalID, parmSettingID, parmSetting)
        End Sub

        Public Shared Function WCF_Setting() As String

            Dim strURI As String = ""

            Dim mySqlString As New StringBuilder()
            mySqlString.Append("SELECT top 1 ISNULL(Uri, '') as Uri ")
            mySqlString.Append("FROM {databaseOwner}{objectQualifier}IWebCFConfiguration ")

            Dim dr As IDataReader = CType(DotNetNuke.Data.DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), Nothing), IDataReader)

            If dr IsNot Nothing Then

                While dr.Read()
                    strURI = Convert.ToString(dr("Uri"))
                End While

            End If

            Return strURI

        End Function

        Public Shared Sub DeleteWCF_Setting()
            DotNetNuke.Data.DataProvider.Instance().ExecuteSQL("DELETE FROM {databaseOwner}{objectQualifier}IWebCFConfiguration ", Nothing)
        End Sub

        Public Shared Sub InsertWCF_Setting(ByVal strSetting As String)

            Dim mySqlString As New StringBuilder()

            mySqlString.Append("INSERT INTO {databaseOwner}{objectQualifier}IWebCFConfiguration ")
            mySqlString.Append("(Uri) ")
            mySqlString.Append(" VALUES (@Uri) ")

            Dim parmUri As SqlParameter = New SqlParameter("@Uri", SqlDbType.NVarChar, 500)
            parmUri.Value = Left(strSetting, 500)

            DotNetNuke.Data.DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), parmUri)

        End Sub

#Region "UpdateSecuritySetting"
        Public Shared Sub UpdateSecuritySetting(ByVal PortalID As Integer, ByVal MethodName As String, ByVal SecuritySetting As String)

            Dim mySqlString As New StringBuilder()
            mySqlString.Append("Update {databaseOwner}{objectQualifier}IWebCFMethods ")
            mySqlString.Append("SET SecuritySetting = '" & SecuritySetting & "' ")
            mySqlString.Append("Where (PortalID = " & PortalID & ") ")
            mySqlString.Append("AND (MethodName = '" & MethodName & "') ")

            DotNetNuke.Data.DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), Nothing)
        End Sub
#End Region

#Region "GetMethodsInDatabase"
        Public Shared Function GetMethodInDatabase(ByVal PortalID As Integer, ByVal MethodName As String) As IWEBMethodInfo
            Dim IWEBMethodInfo As New IWEBMethodInfo

            Dim mySqlString As New StringBuilder()
            mySqlString.Append("SELECT PortalID, MethodName, MethodDescription, SecuritySetting, CompanyName, ApplicationName, FirstGrouping, SecondGrouping ")
            mySqlString.Append("FROM {databaseOwner}{objectQualifier}IWebCFMethods ")
            mySqlString.Append("WHERE PortalID = '" & PortalID & "' ")
            mySqlString.Append("AND MethodName = '" & MethodName & "' ")

            Using dr As IDataReader = CType(DotNetNuke.Data.DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), Nothing), IDataReader)
                While dr.Read
                    IWEBMethodInfo.MethodName = Convert.ToString(dr("MethodName"))
                    IWEBMethodInfo.Portal = Convert.ToString(dr("PortalID"))
                    IWEBMethodInfo.MethodDescription = Convert.ToString(dr("MethodDescription"))
                    IWEBMethodInfo.SecuritySetting = Convert.ToString(dr("SecuritySetting"))
                    IWEBMethodInfo.CompanyName = Convert.ToString(dr("CompanyName"))
                    IWEBMethodInfo.ApplicationName = Convert.ToString(dr("ApplicationName"))
                    IWEBMethodInfo.FirstGrouping = Convert.ToString(dr("FirstGrouping"))
                    IWEBMethodInfo.SecondGrouping = Convert.ToString(dr("SecondGrouping"))
                End While
            End Using

            Return IWEBMethodInfo
        End Function
        Public Shared Function GetMethodsInDatabase() As List(Of IWEBMethodInfo)
            Dim IWEBMethodInfoList As New List(Of IWEBMethodInfo)

            Dim mySqlString As New StringBuilder()
            mySqlString.Append("SELECT PortalID, MethodName, MethodDescription, SecuritySetting, CompanyName, ApplicationName, FirstGrouping, SecondGrouping ")
            mySqlString.Append("FROM {databaseOwner}{objectQualifier}IWebCFMethods ")

            mySqlString.Append("ORDER BY PortalID, MethodName ")

            Using dr As IDataReader = CType(DotNetNuke.Data.DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), Nothing), IDataReader)
                While dr.Read
                    Dim IWEBMethodInfo As New IWEBMethodInfo()
                    IWEBMethodInfo.MethodName = Convert.ToString(dr("MethodName"))
                    IWEBMethodInfo.Portal = Convert.ToString(dr("PortalID"))
                    IWEBMethodInfo.MethodDescription = Convert.ToString(dr("MethodDescription"))
                    IWEBMethodInfo.SecuritySetting = Convert.ToString(dr("SecuritySetting"))
                    IWEBMethodInfo.CompanyName = Convert.ToString(dr("CompanyName"))
                    IWEBMethodInfo.ApplicationName = Convert.ToString(dr("ApplicationName"))
                    IWEBMethodInfo.FirstGrouping = Convert.ToString(dr("FirstGrouping"))
                    IWEBMethodInfo.SecondGrouping = Convert.ToString(dr("SecondGrouping"))
                    IWEBMethodInfoList.Add(IWEBMethodInfo)
                End While
            End Using
            Return IWEBMethodInfoList
        End Function
        Public Shared Function GetMethodsInDatabase(ByVal Portal As Integer) As ArrayList
            Dim IWEBMethodInfoList As New ArrayList()

            Dim mySqlString As New StringBuilder()
            mySqlString.Append("SELECT MethodName ")
            mySqlString.Append("FROM {databaseOwner}{objectQualifier}IWebCFMethods ")
            mySqlString.Append("Where PortalID = " & Portal & " ")

            Using dr As IDataReader = CType(DotNetNuke.Data.DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), Nothing), IDataReader)
                While dr.Read
                    IWEBMethodInfoList.Add(Convert.ToString(dr("MethodName")))
                End While
            End Using
            Return IWEBMethodInfoList
        End Function

        Public Shared Function GetIWEBMethodInfoMethodsInDatabase(ByVal PortalID As Integer) As List(Of IWEBMethodInfo)
            Dim IWEBMethodInfoList As New List(Of IWEBMethodInfo)

            Dim mySqlString As New StringBuilder()
            mySqlString.Append("SELECT PortalID, MethodName, MethodDescription, SecuritySetting, CompanyName, ApplicationName, FirstGrouping, SecondGrouping ")
            mySqlString.Append("FROM {databaseOwner}{objectQualifier}IWebCFMethods ")
            mySqlString.Append("WHERE PortalID = @PortalID ")
            mySqlString.Append("ORDER BY PortalID, MethodName ")

            Dim prmPortalID As SqlParameter = New SqlParameter("@PortalID", SqlDbType.Int, 4)
            prmPortalID.Value = PortalID

            Using dr As IDataReader = CType(DotNetNuke.Data.DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), prmPortalID), IDataReader)
                While dr.Read
                    Dim IWEBMethodInfo As New IWEBMethodInfo()
                    IWEBMethodInfo.MethodName = Convert.ToString(dr("MethodName"))
                    IWEBMethodInfo.Portal = Convert.ToString(dr("PortalID"))
                    IWEBMethodInfo.MethodDescription = Convert.ToString(dr("MethodDescription"))
                    IWEBMethodInfo.SecuritySetting = Convert.ToString(dr("SecuritySetting"))
                    IWEBMethodInfo.CompanyName = Convert.ToString(dr("CompanyName"))
                    IWEBMethodInfo.ApplicationName = Convert.ToString(dr("ApplicationName"))
                    IWEBMethodInfo.FirstGrouping = Convert.ToString(dr("FirstGrouping"))
                    IWEBMethodInfo.SecondGrouping = Convert.ToString(dr("SecondGrouping"))
                    IWEBMethodInfoList.Add(IWEBMethodInfo)
                End While
            End Using
            Return IWEBMethodInfoList
        End Function
#End Region

#Region "RemovePortals"
        Public Shared Sub RemoveEntries(ByVal PortalsOnServer As Array, ByVal colAssemblies As List(Of IWEBMethodInfo))
            Dim objIWEBMethodInfo As IWEBMethodInfo
            Dim strPortalsToRemove As String = ""
            Dim strMethodsToRemove As String = ""
            Dim strPortal As String = ""
            Dim strPortals As String = ""
            Dim strMethods As String = ""

            'Build Where clause
            For Each strPortal In PortalsOnServer
                strPortals &= strPortal & ","
            Next

            If strPortals <> "" Then
                'Strip off the last comma
                strPortals = strPortals.Substring(0, (strPortals.Length - 1))
            End If

            For Each objIWEBMethodInfo In colAssemblies
                strMethods &= "'" & objIWEBMethodInfo.MethodName.Replace("'", "") & "',"
            Next

            If strMethods <> "" Then
                'Strip off the last comma
                strMethods = strMethods.Substring(0, (strMethods.Length - 1))
            End If

            Dim mySqlString As New StringBuilder()
            mySqlString.Append("Delete ")
            mySqlString.Append("FROM {databaseOwner}{objectQualifier}IWebCFMethods ")
            mySqlString.Append("Where PortalID not in (" & strPortals & ") ")
            mySqlString.Append("Or MethodName not in (" & strMethods & ")")

            DotNetNuke.Data.DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), Nothing)
        End Sub
#End Region

#Region "AddMethod"
        Public Shared Sub AddMethod(ByVal objIWEBMethodInfo As IWEBMethodInfo)

            Dim mySqlString As New StringBuilder()
            mySqlString.Append("INSERT INTO {databaseOwner}{objectQualifier}IWebCFMethods ")
            mySqlString.Append("(PortalID, MethodName, MethodDescription, SecuritySetting, CompanyName, ApplicationName, FirstGrouping, SecondGrouping) ")
            mySqlString.Append("VALUES (@PortalID,@MethodName,@MethodDescription,@SecuritySetting,@CompanyName,@ApplicationName,@FirstGrouping,@SecondGrouping)")

            Dim prmPortalID As SqlParameter = New SqlParameter("@PortalID", SqlDbType.Int, 4)
            Dim prmMethodName As SqlParameter = New SqlParameter("@MethodName", SqlDbType.NVarChar, 150)
            Dim prmSecuritySetting As SqlParameter = New SqlParameter("@SecuritySetting", SqlDbType.NVarChar, 100)
            Dim prmCompanyName As SqlParameter = New SqlParameter("@CompanyName", SqlDbType.NVarChar, 100)
            Dim prmApplicationName As SqlParameter = New SqlParameter("@ApplicationName", SqlDbType.NVarChar, 100)
            Dim prmFirstGrouping As SqlParameter = New SqlParameter("@FirstGrouping", SqlDbType.NVarChar, 100)
            Dim prmSecondGrouping As SqlParameter = New SqlParameter("@SecondGrouping", SqlDbType.NVarChar, 100)
            Dim prmMethodDescription As SqlParameter = New SqlParameter("@MethodDescription", SqlDbType.NVarChar, 100)

            prmPortalID.Value = objIWEBMethodInfo.Portal
            prmMethodName.Value = objIWEBMethodInfo.MethodName
            prmSecuritySetting.Value = objIWEBMethodInfo.SecuritySetting
            prmCompanyName.Value = objIWEBMethodInfo.CompanyName
            prmApplicationName.Value = objIWEBMethodInfo.ApplicationName
            prmFirstGrouping.Value = objIWEBMethodInfo.FirstGrouping
            prmSecondGrouping.Value = objIWEBMethodInfo.SecondGrouping
            prmMethodDescription.Value = objIWEBMethodInfo.MethodDescription

            DotNetNuke.Data.DataProvider.Instance().ExecuteSQL(mySqlString.ToString(), prmPortalID, prmMethodName, prmMethodDescription, prmSecuritySetting, prmCompanyName, prmApplicationName, prmFirstGrouping, prmSecondGrouping)
        End Sub
#End Region

    End Class
End Namespace